package be.jprojects.subscription.dataaccess.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import be.jprojects.subscription.dataaccess.CityDataAccess;
import be.jprojects.subscription.dataaccess.CountryDataAccess;
import be.jprojects.subscription.dataaccess.ProvinceDataAccess;
import be.jprojects.subscription.dataaccess.StreetDataAccess;
import be.jprojects.subscription.logic.City;
import be.jprojects.subscription.logic.Country;
import be.jprojects.subscription.logic.PostalCode;
import be.jprojects.subscription.logic.Province;
import be.jprojects.subscription.logic.Street;
import be.jprojects.subscription.logic.interfaces.ICity;
import be.jprojects.subscription.logic.interfaces.ICountry;
import be.jprojects.subscription.logic.interfaces.IPostalCode;
import be.jprojects.subscription.logic.interfaces.IProvince;
import be.jprojects.subscription.logic.interfaces.IStreet;

public class MySQLGeoDataAccess implements CountryDataAccess, CityDataAccess, ProvinceDataAccess, StreetDataAccess{
	private MySQLDataAccessObjectFactory mySQLDataAccessObjectFactory;

	private List<ICountry> countries;
	
	public MySQLGeoDataAccess(MySQLDataAccessObjectFactory mySQLDataAccessObjectFactory) {
		setMySQLDataAccessObjectFactory(mySQLDataAccessObjectFactory);
		countries = new ArrayList<ICountry>();
	}

	public MySQLDataAccessObjectFactory getMySQLDataAccessObjectFactory() {
		return mySQLDataAccessObjectFactory;
	}

	public void setMySQLDataAccessObjectFactory(
			MySQLDataAccessObjectFactory mySQLDataAccessObjectFactory) {
		this.mySQLDataAccessObjectFactory = mySQLDataAccessObjectFactory;
	}


	@Override
	public boolean deleteCountry(ICountry country) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public ICountry findCountry() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<ICountry> getCountries() throws Exception {
		if (countries.size() == 0) {
			Connection c = mySQLDataAccessObjectFactory.getConnection();
			PreparedStatement pStatement = c.prepareStatement(mySQLDataAccessObjectFactory.getBundle().getString("getCountries"));
			ResultSet rSet = pStatement.executeQuery();
			while (rSet.next()) {
				ICountry country = mapCountry(rSet);
				country.setFullyLoaded(false);
				countries.add(country);
			}
		}
		return countries;
	}

	@Override
	public int insertCountry(ICountry country) throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public boolean updateCountry(ICountry country) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}
	
	private ICountry mapCountry(ResultSet set) throws SQLException {
		ICountry country = new Country();
		country.setId(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("countryid")));
		country.setName(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("countryname"))); 
		country.setIso3(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("iso3")));
		return country;
	}

	@Override
	public boolean deleteCity(ICity city) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public ICity findCity() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void loadProvinces(ICountry country) throws Exception {
		Connection c = mySQLDataAccessObjectFactory.getConnection();
		PreparedStatement pStatement = c.prepareStatement(mySQLDataAccessObjectFactory.getBundle().getString("getProvincesForCountry"));
		pStatement.setString(1, country.getId());
		ResultSet rSet = pStatement.executeQuery();
		while (rSet.next()) {
			IProvince province = mapProvince(rSet);
			province.setCountry(country);
			province.setFullyLoaded(false);
			country.getProvinces().add(province);
		}
		country.setFullyLoaded(true);
	}

	@Override
	public int insertCity(ICity city) throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public boolean updateCity(ICity city) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}
	
	private ICity mapCity(ResultSet set) throws SQLException {
		ICity c = new City();
		c.setId(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("cityid")));
		c.setName(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("cityname"))); 
		IPostalCode pc = new PostalCode();
		pc.setCity(c);
		pc.setCode(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("postalcode")));
		c.getPostalCodes().add(pc);
		return c;
	}

	@Override
	public void loadCities(IProvince province) throws Exception {
		Connection c = mySQLDataAccessObjectFactory.getConnection();
		PreparedStatement pStatement = c.prepareStatement(mySQLDataAccessObjectFactory.getBundle().getString("getCitiesForProvince"));
		pStatement.setString(1, province.getId());
		ResultSet rSet = pStatement.executeQuery();
		while (rSet.next()) {
			ICity city = mapCity(rSet);
			city.setProvince(province);
			city.setFullyLoaded(false);
			province.getCities().add(city);
		}
		province.setFullyLoaded(true);
	}

	@Override
	public boolean deleteProvince(IProvince city) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public IProvince findProvince() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int insertProvince(IProvince province) throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public boolean updateProvince(IProvince city) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	private IProvince mapProvince(ResultSet set) throws SQLException {
		IProvince province = new Province();
		province.setId(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("provinceid")));
		province.setName(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("provincename"))); 
		return province;
	}
	
	private IStreet mapStreet(ResultSet set) throws SQLException {
		IStreet street = new Street();
		street.setId(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("streetid")));
		street.setName(set.getString(mySQLDataAccessObjectFactory.getBundle().getString("streetname"))); 
		return street;
	}

	@Override
	public boolean deleteStreet(IStreet city) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public IStreet findStreet() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void loadStreets(ICity city) throws Exception {
		Connection c = mySQLDataAccessObjectFactory.getConnection();
		PreparedStatement pStatement = c.prepareStatement(mySQLDataAccessObjectFactory.getBundle().getString("getStreetsForCity"));
		//TODO loop over de verschillende postcodes van de gemeente;
		pStatement.setString(1, city.getPostalCodes().get(0).getCode());
		ResultSet rSet = pStatement.executeQuery();
		while (rSet.next()) {
			IStreet street = mapStreet(rSet);
			street.setCity(city);
			street.setFullyLoaded(false);
			city.getStreets().add(street);
		}
		city.setFullyLoaded(true);
	}

	@Override
	public int insertStreet(IStreet street) throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public boolean updateStreet(IStreet city) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}
}
